%let te = 38;
%let y = %sysfunc(mod(&base_yr,100));



%macro get_nf_universe;

	
	proc printto log = "&logdir/get_nf_universe.txt" new;
	run;
	
	
	
	%let te = 38;
	
	
		/****************************************
		*										*
		*			Start PROC SQL				*
		*										*
		****************************************/
		proc sql noprint;
		[connection details redacted]
			
			create table info_returns as
			select * from connection to iq (	

				
				/* info returns other than 1095 */
				select distinct
					a.[redacted] as person_id,
					a.[redacted] as st,
					b.[redacted] as ssn,
					c.[redacted] as sex,
					c.[redacted] as date_of_birth
				from [redacted] as a
					inner join [redacted] as b 
						on(a.[redacted] = b.[redacted])	
					inner join [redacted] as c 
						on(b.[redacted] = c.[redacted])
				where a.[redacted] = &base_yr
					and mod(a.[redacted],1000) = &te
					and a.[redacted] <= 4
					and a.[redacted] > 0
					and a.[redacted] is not null
					and a.[redacted] ~= "**"
					and &base_yr - floor(c.[redacted]/10000) between 18 and 105
			);

			%put Done non 1095;

			create table f1095 as
			select * from connection to iq (	


				select distinct
					a.[redacted] as ssn,
					b.[redacted] as st,
					c.[redacted] as person_id,
					d.[redacted] as sex,
					d.[redacted] as date_of_birth
				from [redacted] as a
					inner join [redacted] as b 
						on(a.[redacted] = b.[redacted]
							and a.[redacted] = b.[redacted])
					inner join [redacted] as c 
						on(a.[redacted] = c.[redacted])
					inner join [redacted] as d 
						on(a.[redacted] = d.[redacted])
				where a.[redacted] = &base_yr 
					and mod(c.[redacted],1000) = &te
					and (a.[redacted] is null or a.[redacted] ~= "BUSINESS_TIN")
					and b.[redacted] is not null
					and b.[redacted] ~= "**"
					and &base_yr - floor(d.[redacted]/10000) between 18 and 105

			);
			disconnect from iq;
			quit;

		
			
		/* restrict to 50 states and DC, then drop dups */

		%let statelist = AL AK AZ AR CA CO CT DE DC FL GA HI ID IL IN IA KS KY LA ME MD MA MI MN MS MO MT
			NE NV NH NJ NM NY NC ND OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY;

		data info_returns;
			set info_returns;
			tokeep = 0;
			%do i = 1 %to 51;
				%let st = %sysfunc(scan(&statelist,&i));
				if st = "&st" then tokeep = 1;
			%end;
			if tokeep;
			drop tokeep st;
		run;

		data f1095;
			set f1095;
			tokeep = 0;
			%do i = 1 %to 51;
				%let st = %sysfunc(scan(&statelist,&i));
				if st = "&st" then tokeep = 1;
			%end;
			if tokeep;
			drop tokeep st;
		run;

		proc sort data = info_returns nodupkey;
			by person_id;
		run;
		proc sort data = f1095 nodupkey;
			by person_id;
		run;

		data rlib.nf_universe;
			merge info_returns(in=a) f1095(in=b);
			by person_id;
			info_return = a;
			f1095 = b;
		run;

		proc freq data = rlib.nf_universe;
			tables info_return*f1095;
		run;

	proc printto;
	run;

%mend;
		
					
					